﻿using System;
using System.Data;

namespace ConsoleApp1.function
{
    public class sqlserver
    {
        public static void getTables(string server, string db)
        {
            try
            {
                SqlServerHelper.strConnString = "server=" + server + ";database=" + db + ";Trusted_Connection = True;";

                Console.WriteLine("@@@@@@@@@@@@@@@@@@@@@@@ 所有表");
                string strSql1 = "use " + db + ";" + "select * from sysobjects where type='U' and name<>'sysdiagrams'";
                var Reader1 = SqlServerHelper.GetReader(strSql1);
                while (Reader1.Read())
                {
                    Console.WriteLine(Reader1[0].ToString());
                }
            }
            catch (Exception)
            {
                Console.WriteLine("connect failed");
            }
        }

        public static void checkConnect(string server)
        {
            try
            {
                SqlServerHelper.strConnString = "server=" + server + ";Trusted_Connection = True;";

                Console.WriteLine("@@@@@@@@@@@@@@@@@@@@@@@ 所有库");
                string strSql = "select * from sys.sysdatabases";
                var sqlDataReader = SqlServerHelper.GetReader(strSql);
                while (sqlDataReader.Read())
                {
                    Console.WriteLine(sqlDataReader[0].ToString());
                }
            }
            catch (Exception)
            {
                Console.WriteLine("connect failed");
            }
        }

        public static void createVmFromDb(string server, string db, string strTable)
        {
            SqlServerHelper.strConnString = "server=" + server + ";database=" + db + ";Trusted_Connection = True;";
            Console.WriteLine("@@@@@@@@@@@@@@@@@@@@@@@ 所有列");
            string strSql2 = "use " + db +
                ";select   a.[name] as lieming,b.[name] as leixing    from   syscolumns a,systypes b  where   a.id=object_id('"
                        + db + ".." + strTable + "') and a.xtype=b.xtype and    b.[name]<>'sysname'";
            DataTable dt = SqlServerHelper.getDataTable(strSql2);
            foreach (DataRow dr in dt.Rows)
            {
                string field = dr["lieming"].ToString();
                string type = dr["leixing"].ToString();

                //Console.WriteLine(field);
                //Console.WriteLine(type);

                if (type == "datetime2")
                {
                    string s = " public DateTime " + field + " { get; set; }";
                    Console.WriteLine(s);
                }
                else if (type == "int")
                {
                    string s = " public int " + field + " { get; set; }";
                    Console.WriteLine(s);
                }
                else if (type == "bit")
                {
                    string s = " public bool " + field + " { get; set; }";
                    Console.WriteLine(s);
                }
                else if (type == "nvarchar")
                {
                    string s = " public string " + field + " { get; set; }";
                    Console.WriteLine(s);
                }
                else
                {
                    string s = " public string " + field + " { get; set; }";
                    Console.WriteLine(s);
                }
            }
        }
    }
}